package com.cndw.kungfu.business;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.support.JdbcUtils;

import com.cndw.kungfu.comm.AccessFlag;
import com.cndw.kungfu.comm.GmCommand;
import com.cndw.kungfu.config.ConstantsFlag;
import com.cndw.rpg.annotation.Controller;
import com.cndw.rpg.annotation.UnAuth;
import com.cndw.rpg.framework.ResponseMessage;
import com.cndw.rpg.framework.Session;

@Controller
public class BusinessApiData {

	@Autowired
	private DataSource dataSource;

	@UnAuth
	@GmCommand
	@AccessFlag(ConstantsFlag.apiData)
	public ResponseMessage getDatas(Session session, String sign, String stmt) {

		//String[] filterSQLs = { "INSERT", "UPDATE", "DELETE", "CREATE", "DROP", "TRUNCATE", "EXEC", "DELIMITER","ALTER" };

		String[] querys = stmt.split(";");
		for (String q : querys) {
//			for (String filter : filterSQLs) {
//				if (q.trim().toUpperCase().startsWith(filter)) {
//					JSONObject jsonObject = new JSONObject();
//					jsonObject.put("status", -1);
//					return ResponseMessage.getFailMessage(jsonObject.toString());
//				}
//			}
			if (!q.toLowerCase().startsWith("show") && !q.toLowerCase().startsWith("select")) {
				JSONObject jsonObject = new JSONObject();
				jsonObject.put("status", -1);
				return ResponseMessage.getFailMessage(jsonObject.toString());
			}
		}
		JSONObject jsonObject = new JSONObject();

		Connection con = DataSourceUtils.getConnection(dataSource);
		Statement stmtToUse = null;
		try {
			Connection conToUse = con;
			stmtToUse = conToUse.createStatement();

			String query = stmt;

			ResultSet result = stmtToUse.executeQuery(query);

			ResultSetMetaData rsmd = (ResultSetMetaData) result.getMetaData();
			int colCount = rsmd.getColumnCount();
			JSONArray colArray = new JSONArray();

			for (int i = 1; i <= colCount; i++) {
				JSONObject colJsonObject = new JSONObject();
				colJsonObject.put(rsmd.getColumnLabel(i), rsmd.getColumnTypeName(i) + "(" + rsmd.getColumnDisplaySize(i) + ")");
				colArray.add(colJsonObject);

			}

			jsonObject.put("status", 1);
			JSONArray jArray = new JSONArray();
			while (result.next()) {
				JSONArray dArray = new JSONArray();
				for (int i = 1; i <= colCount; i++) {
					dArray.add(result.getObject(i));
				}
				jArray.add(dArray);
			}
			jsonObject.put("count", jArray.size());
			jsonObject.put("columns", colArray);
			jsonObject.put("data", jArray);
		} catch (SQLException ex) {
			JdbcUtils.closeStatement(stmtToUse);
			stmtToUse = null;
			DataSourceUtils.releaseConnection(con, dataSource);
			con = null;

			jsonObject.put("status", 0);
			jsonObject.put("info", ex.toString());
		} finally {
			JdbcUtils.closeStatement(stmtToUse);
			DataSourceUtils.releaseConnection(con, dataSource);
		}

		return ResponseMessage.getSuccMessage(jsonObject.toString());
	}

}
